Export_to_excel script slows down terribly

Has anyone ever been able to prevent the inevitable slow down that occurs when exporting to excel a large number of DOORS objects?

I have placed most OLEAutoArgs variables global to prevent them from being re-created for each object that is exported, however this is not enough of an improvement.

In word there is a call to :

oleMethod (objDoc, "UndoClear")
to clear the undo data-structure. However, no where can I find the EXCEL equivalent and a small snippet of code on its usage.
PatrickGuay - Wed Dec 21 07:47:58 EST 2011

Re: Export_to_excel script slows down terribly
Mathias Mamsch - Wed Dec 21 09:40:00 EST 2011

My guess is, the slowdown probably occurs due to bad memory management (not freeing up ressources properly). See also that post:

https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14672594&#14672594

Replacing stuff by global variables might not be enough! In addition exporting large data sets is always prone for filling up the string table pretty badly. But it is really hard to say more, one would need to look into the script.

Regards, Mathias
Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Export_to_excel script slows down terribly
llandale - Wed Dec 21 11:46:42 EST 2011

When exporting to Word there was drastic improvement when you turned off Spell/Grammer checking (especially the "show errors" part) and background repagination. I suppose tracking changes and a couple others would be no brainers to turn off.

Perhaps start turning off some such features in Excel, and when you find an offender then figure out how to turn that off from your script.

-Louie

Re: Export_to_excel script slows down terribly
OurGuest - Wed Dec 21 13:12:39 EST 2011

If your situation permits, one of the easiest method to speed up scripts is cut the processing into smaller chunks.

For example: Instead of processing 100 modules with one execution of a script, process 50 modules with two execution of a script. Or even better with different DOORS sessions.

Re: Export_to_excel script slows down terribly
PatrickGuay - Wed Dec 21 14:21:32 EST 2011

OurGuest - Wed Dec 21 13:12:39 EST 2011
If your situation permits, one of the easiest method to speed up scripts is cut the processing into smaller chunks.

For example: Instead of processing 100 modules with one execution of a script, process 50 modules with two execution of a script. Or even better with different DOORS sessions.

This is the type of thing I have been doing in the past. However, it is prone to errors since the document is live, people are adding and removing objects every day. This means that I have to freeze the module and create filters or views and run the export on these views and then merge the data togeather... kinda annoying

Hard to believe that this script has been around since the early 2000s and still there is no work-around or fix. Even the author of the script does not have any suggestions for me !!

Re: Export_to_excel script slows down terribly
OurGuest - Wed Dec 21 14:44:34 EST 2011

PatrickGuay - Wed Dec 21 14:21:32 EST 2011
This is the type of thing I have been doing in the past. However, it is prone to errors since the document is live, people are adding and removing objects every day. This means that I have to freeze the module and create filters or views and run the export on these views and then merge the data togeather... kinda annoying

Hard to believe that this script has been around since the early 2000s and still there is no work-around or fix. Even the author of the script does not have any suggestions for me !!

It is unlikely that anyone can look into a black hole and give you detailed solution -- you will have to help others help you by doing one of the following:

1. Post code.
or
2. Post a detailed specification of the function of what is to be done. (The specification shouldn't tell how the original author solved the problem) Perhaps with an unbiased specification someone can provide alternate solutions that can be implemented by your team.

Re: Export_to_excel script slows down terribly
Mathias Mamsch - Wed Dec 21 17:30:47 EST 2011

OurGuest - Wed Dec 21 13:12:39 EST 2011
If your situation permits, one of the easiest method to speed up scripts is cut the processing into smaller chunks.

For example: Instead of processing 100 modules with one execution of a script, process 50 modules with two execution of a script. Or even better with different DOORS sessions.

I think running a script with smaller chunks is a questionable way of speeding things up. In our team we have been struggeling a lot with exports of large data sets. The only way I know of to make DOORS slower over time is filling up the allocation list. The below example is a small demonstration of the effect. The script just accesses the Worksheets collection of the excel instance over and over again, without actually doing anything. You will notice from the times on the progressbar it gets slower and slower. Commenting in the oleAutoCloseObject will remove the slowdown. In my experience almost all code for DOORS that you will find especially when it is around for several years ignores the fact, that every property that you read from OLE, that again returns an OLE object will in fact create a new object, that if not properly freed will, slow down your program more and more. You will also find that mistake in native DOORS code. I am pretty sure, that if you post the script you are talking about, that we can find the issue and resolve it.

Regards, Mathias
  

pragma runLim, 0 
OleAutoObj objExcel = oleGetAutoObject "Excel.Application" 
print olePut(objExcel, "visible", true) 
OleAutoObj objWorksheets = null
 
int Iterations = 1200
progressStart(dbExplorer, "Slowdown Experiment", "", Iterations )
    int i, j
    for i in 1:Iterations do {
    int startTime = getTickCount_ () 
    for j in 1:100 do {
                oleGet(objExcel, "Worksheets", objWorksheets)
 
                // oleCloseAutoObject objWorksheets  // <<- comment me in
 
                if (progressCancelled) {
                    if (confirm("Exit loop?")) {
                        progressStop
                        halt
                    }
                }
        } 
        progressStep i
        int duration = getTickCount_() - startTime
        progressMessage "Time for 100 iterations:" duration " ms"
    }
    progressStop 
 
 
oleCloseAutoObject objExcel; objExcel = null

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Re: Export_to_excel script slows down terribly
OurGuest - Thu Dec 22 08:05:23 EST 2011

Mathias Mamsch - Wed Dec 21 17:30:47 EST 2011

I think running a script with smaller chunks is a questionable way of speeding things up. In our team we have been struggeling a lot with exports of large data sets. The only way I know of to make DOORS slower over time is filling up the allocation list. The below example is a small demonstration of the effect. The script just accesses the Worksheets collection of the excel instance over and over again, without actually doing anything. You will notice from the times on the progressbar it gets slower and slower. Commenting in the oleAutoCloseObject will remove the slowdown. In my experience almost all code for DOORS that you will find especially when it is around for several years ignores the fact, that every property that you read from OLE, that again returns an OLE object will in fact create a new object, that if not properly freed will, slow down your program more and more. You will also find that mistake in native DOORS code. I am pretty sure, that if you post the script you are talking about, that we can find the issue and resolve it.

Regards, Mathias
  

pragma runLim, 0 
OleAutoObj objExcel = oleGetAutoObject "Excel.Application" 
print olePut(objExcel, "visible", true) 
OleAutoObj objWorksheets = null
 
int Iterations = 1200
progressStart(dbExplorer, "Slowdown Experiment", "", Iterations )
    int i, j
    for i in 1:Iterations do {
    int startTime = getTickCount_ () 
    for j in 1:100 do {
                oleGet(objExcel, "Worksheets", objWorksheets)
 
                // oleCloseAutoObject objWorksheets  // <<- comment me in
 
                if (progressCancelled) {
                    if (confirm("Exit loop?")) {
                        progressStop
                        halt
                    }
                }
        } 
        progressStep i
        int duration = getTickCount_() - startTime
        progressMessage "Time for 100 iterations:" duration " ms"
    }
    progressStop 
 
 
oleCloseAutoObject objExcel; objExcel = null

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

This code doesn't work?

Why did you post?

Re: Export_to_excel script slows down terribly
Mathias Mamsch - Thu Dec 22 08:08:18 EST 2011

OurGuest - Thu Dec 22 08:05:23 EST 2011
This code doesn't work?

Why did you post?

I should have said, that you need to start Excel before you start that code. Or you change getOleAutoObject to createOleAutoObject. Otherwise you probably will get a null parameter error. If you got another error, can you post the message? It worked fine on DOORS 8.2, i did not test on DOORS 9. Sorry for that, I assumed that people actually read the code, when something goes wrong. Regards, Mathias
Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Export_to_excel script slows down terribly
PatrickGuay - Thu Dec 22 09:09:31 EST 2011

I have supplied the script I am working on. It is the enhanced_export_to_excel.dxl with slight modifications that are used to create filters in order to attempt to lessen the load of DOORS. The filter grabs a preset number of objects and exports them. I then try to release some memory. The script slows down after 1000 objects and there are around 9000 objects in my module (Sorry I cannot supply this module).
Attachments

attachment_14771697_export_to_excel_NH90.dxl

Re: Export_to_excel script slows down terribly
PatrickGuay - Thu Dec 22 09:17:51 EST 2011

Mathias Mamsch - Wed Dec 21 17:30:47 EST 2011

I think running a script with smaller chunks is a questionable way of speeding things up. In our team we have been struggeling a lot with exports of large data sets. The only way I know of to make DOORS slower over time is filling up the allocation list. The below example is a small demonstration of the effect. The script just accesses the Worksheets collection of the excel instance over and over again, without actually doing anything. You will notice from the times on the progressbar it gets slower and slower. Commenting in the oleAutoCloseObject will remove the slowdown. In my experience almost all code for DOORS that you will find especially when it is around for several years ignores the fact, that every property that you read from OLE, that again returns an OLE object will in fact create a new object, that if not properly freed will, slow down your program more and more. You will also find that mistake in native DOORS code. I am pretty sure, that if you post the script you are talking about, that we can find the issue and resolve it.

Regards, Mathias
  

pragma runLim, 0 
OleAutoObj objExcel = oleGetAutoObject "Excel.Application" 
print olePut(objExcel, "visible", true) 
OleAutoObj objWorksheets = null
 
int Iterations = 1200
progressStart(dbExplorer, "Slowdown Experiment", "", Iterations )
    int i, j
    for i in 1:Iterations do {
    int startTime = getTickCount_ () 
    for j in 1:100 do {
                oleGet(objExcel, "Worksheets", objWorksheets)
 
                // oleCloseAutoObject objWorksheets  // <<- comment me in
 
                if (progressCancelled) {
                    if (confirm("Exit loop?")) {
                        progressStop
                        halt
                    }
                }
        } 
        progressStep i
        int duration = getTickCount_() - startTime
        progressMessage "Time for 100 iterations:" duration " ms"
    }
    progressStop 
 
 
oleCloseAutoObject objExcel; objExcel = null

 

 

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

 

 

Great example Mathias, ca prouve ton point. However, is there any way for me to know more of these functions that you are using such as oleCloseAutoObject objWorksheets.

I am basically staying in the same worksheet therefore this will not help me much.

Re: Export_to_excel script slows down terribly
OurGuest - Thu Dec 22 10:56:27 EST 2011

PatrickGuay - Thu Dec 22 09:17:51 EST 2011
Great example Mathias, ca prouve ton point. However, is there any way for me to know more of these functions that you are using such as oleCloseAutoObject objWorksheets.

I am basically staying in the same worksheet therefore this will not help me much.

I look a little and it seems like your problem may be in the function CreateReasonableFilter.

I have one object in my module -- this function is chewing up time.

Re: Export_to_excel script slows down terribly
OurGuest - Thu Dec 22 12:33:17 EST 2011

OurGuest - Thu Dec 22 10:56:27 EST 2011
I look a little and it seems like your problem may be in the function CreateReasonableFilter.

I have one object in my module -- this function is chewing up time.

The createReasonableFilter can be an infinit loop which should be fixed.

Also, I would suggest have some method to disable progress bars -- the way progress bars are implemented in DOORS can slow down the dxl because of use of strings.

Re: Export_to_excel script slows down terribly
OurGuest - Thu Dec 22 13:30:55 EST 2011

OurGuest - Thu Dec 22 12:33:17 EST 2011
The createReasonableFilter can be an infinit loop which should be fixed.

Also, I would suggest have some method to disable progress bars -- the way progress bars are implemented in DOORS can slow down the dxl because of use of strings.

My suspect that repeated calls to createReasonableFilter sets up conditions that is causing your major problems. The Progress bar is secondary problem.

Re: Export_to_excel script slows down terribly
Mathias Mamsch - Thu Dec 22 21:52:55 EST 2011

PatrickGuay - Thu Dec 22 09:09:31 EST 2011
I have supplied the script I am working on. It is the enhanced_export_to_excel.dxl with slight modifications that are used to create filters in order to attempt to lessen the load of DOORS. The filter grabs a preset number of objects and exports them. I then try to release some memory. The script slows down after 1000 objects and there are around 9000 objects in my module (Sorry I cannot supply this module).

Ok, I need to rant a little bit here. When it comes to performance optimization it seems there always comes the "crystal ball" effect. People start doing optimizations by trial and error, messing up their code even more. DXL has its very own set of rules when it comes to performance. I want to state something very clear: Optimization without analysis is not going to work 90% of the time. Before doing any performance optimization in DXL code, always make sure you exactly determine the part of the code, where the most of the time is spent. Then optimize specifically. People bring on the wildest guesses about performance, blaming progress bars and strings, etc. I have seen many bogus optimization attempts, where people tried speeding up code which did not work, but they still left their "optimizations" in the code. The best example is a misunderstanding about strings in DXL.

Ok ranting being over I found a couple of memory leaks in the code of the exporter:

  • plainText is not freed in 'stripRichText'
  • 'excelSetCellContents' does not free OleAutoObj Cell returned from 'excelSetCellOLE'
  • 'excelSetCellRichText' does not free font.
  • 'UndoClear' was added without cleaning up 'objDoc'
  • 'excelGetFontOLE' does not cleanup 'objExcelChars'.

Having corrected these should greatly improve on the slow down problem. The tricky thing with these leaks is, you will not remove the slowdown until you resolve all of them. Since I did not use static analysis, but a runtime detector I may have only found leaks that were noticable using my test data. I did for example not test pictures, OLEs or tables, so there might still be leaks in the functions processing these, which were not called during my tests.

I really do not get the 'createReasonableFilter'. If this is a try to resolve performance/resource issues, this seems like the wrong approach and pretty useless. If this is about exporting only a subset of the module or exporting to multiple pages or something I simply did not get it. Anyway the approach of createReasonableFilter using 'Filters' for determining a split for the module is baaad and slow. Additionally hardcoding the number of objects of the module in the code makes me want to cry. It seems like torture to that nice script.

If your problems are not completly resolved, just post back. Additionally you might want to tell something about the export options and the structure of the module you are exporting (number of tables, pictures, OLEs, objects, data size, etc.).

Hope that helps, regards, Mathias
Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
Attachments

attachment_14771922_export_to_excel_NH90_mma.dxl

Re: Export_to_excel script slows down terribly
PatrickGuay - Thu Jan 05 10:03:48 EST 2012

I have to agree that the createReasonableFilter is NOT a good optimization by any means. I also strongly agree with your ranting Mathias about leaving faulty optimizations in code when they do NOT work.

I used that method initially to create views that would limit the number of objects to a reasonable amount of data that I could work with. Say 1000 objects. Therefore, once the views were created for a module of around 8000 objects (+/- 8 views ) I could simply export the 8 views seperately and then merge the sheets into a single excel sheet.

Sorry for the confusion (o:`,

Re: Export_to_excel script slows down terribly
PatrickGuay - Thu Jan 05 10:13:13 EST 2012

Thank you for glazing my code and taking time to analyze possible leaks. It is greatly appreciated

Re: Export_to_excel script slows down terribly
Mathias Mamsch - Thu Jan 05 16:58:33 EST 2012

PatrickGuay - Thu Jan 05 10:13:13 EST 2012
Thank you for glazing my code and taking time to analyze possible leaks. It is greatly appreciated

Well is your slowdown problem resolved then? Regards, Mathias
Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Export_to_excel script slows down terribly
PatrickGuay - Fri Jan 06 07:58:59 EST 2012

yes, your corrections did the trick and although there is still a slight slow down after say 2000 - 3000 objects with approx. 15 columns, I can export the data more efficiently.